package uiao;

import org.junit.Before;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class BankFixture {

    protected final DataSource dataSource = DataSourceFactory.createDataSource();

    @Before
    public void setUp() throws SQLException {
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();

        statement.execute("DROP TABLE IF EXISTS test1");
        statement.execute("DROP TABLE IF EXISTS test2");
        statement.execute("CREATE TABLE test1 (\n" +
                "user_id INT,\n" +
                "amount INT,\n" +
                "PRIMARY KEY(user_id)\n" +
                ");");

        statement.execute("CREATE TABLE test2 (\n" +
                "user_id INT,\n" +
                "amount INT,\n" +
                "PRIMARY KEY(user_id)\n" +
                ");");

        statement.execute("INSERT INTO test1 VALUES (5, 1000);");
        statement.execute("INSERT INTO test2 VALUES (5, 1000);");

        statement.close();
        connection.close();
    }

    protected int getBankAmount(int bankId) throws SQLException {
        Connection connection = dataSource.getConnection();
        PreparedStatement selectStatement = connection.prepareStatement("SELECT amount FROM `test1` WHERE user_id = ?");
        selectStatement.setInt(1, bankId);
        ResultSet resultSet = selectStatement.executeQuery();
        resultSet.next();
        int amount = resultSet.getInt(1);
        resultSet.close();
        selectStatement.close();
        connection.close();
        return amount;
    }

    protected int getInsuranceAmount(int insuranceId) throws SQLException {
        Connection connection = dataSource.getConnection();
        PreparedStatement selectStatement = connection.prepareStatement("SELECT amount FROM `test2` WHERE user_id = ?");
        selectStatement.setInt(1, insuranceId);
        ResultSet resultSet = selectStatement.executeQuery();
        resultSet.next();
        int amount = resultSet.getInt(1);
        resultSet.close();
        selectStatement.close();
        connection.close();
        return amount;
    }

}
